# -*- coding: utf-8 -*-
"""
@Time    : 2024/7/18 15:11 
@Author  : ZhangShenao 
@File    : 1.初始化数据.py 
@Desc    : 使用SQLite数据库,初始化数据
"""
# 导入sqlite3库
import sqlite3

# 连接到SQLite数据库
# 数据被存储到本地文件flower_shop.db中
conn = sqlite3.connect('flower_shop.db')
cursor = conn.cursor()

# 执行SQL,创建`flowers`表
cursor.execute('''
        CREATE TABLE flowers (
            ID INTEGER PRIMARY KEY, 
            Name TEXT NOT NULL, 
            Type TEXT NOT NULL, 
            Source TEXT NOT NULL, 
            PurchasePrice REAL, 
            SalePrice REAL,
            StockQuantity INTEGER, 
            SoldQuantity INTEGER, 
            ExpiryDate DATE,  
            Description TEXT, 
            EntryDate DATE DEFAULT CURRENT_DATE 
        );
    ''')

# 插入鲜花数据
flowers = [
    ('Rose', 'Flower', 'France', 1.2, 2.5, 100, 10, '2023-12-31', 'A beautiful red rose'),
    ('Tulip', 'Flower', 'Netherlands', 0.8, 2.0, 150, 25, '2023-12-31', 'A colorful tulip'),
    ('Lily', 'Flower', 'China', 1.5, 3.0, 80, 5, '2023-12-31', 'An elegant white lily'),
    ('Daisy', 'Flower', 'USA', 0.7, 1.8, 120, 15, '2023-12-31', 'A cheerful daisy flower'),
    ('Orchid', 'Flower', 'Brazil', 2.0, 4.0, 50, 2, '2023-12-31', 'A delicate purple orchid')
]

for flower in flowers:
    cursor.execute('''
        INSERT INTO flowers (Name, Type, Source, PurchasePrice, SalePrice, StockQuantity, SoldQuantity, ExpiryDate, Description) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
    ''', flower)

# 提交事务
conn.commit()

# 关闭数据库连接
conn.close()
